# -*- coding: utf-8 -*-
"""
借阅记录模块所需SQL语句集中
@Author: HuangYu
@E-mail: huangyu@dinton.cn
"""
# 查询借阅记录总数
SELECT_BORROW_RECORDS_COUNT = """
    SELECT COUNT(*)
    FROM borrow_record bc
        LEFT JOIN archives_file af
        ON bc.archive_id = af.id
    WHERE
        1 = 1
"""

# 查询借阅记录列表
SELECT_BORROW_RECORDS = """
    SELECT
        af.serial, af.title, bc.archive_id, bc.id,
        date_format(bc.return_deadline, '%Y-%m-%d') AS return_deadline,
        bc.status,
        CONCAT(ac.code, '柜-', al.code, '格-', ab.code, '盒') AS location,
        bc.borrower, date_format(bc.borrow_time, '%Y-%m-%d') AS borrow_time,
        omc.realname AS operator
    FROM
        borrow_record bc
        LEFT JOIN archives_file af ON bc.archive_id = af.id
        LEFT JOIN archive_box ab ON af.box_id = ab.id
        LEFT JOIN archive_lattice al ON af.lattice_id = al.id
        LEFT JOIN archive_cabinet ac ON af.cabinet_id = ac.id
        LEFT JOIN organize_manager_cloudauthuser omc ON bc.operator_id = omc.id
    WHERE
        1 = 1
"""

# 借阅延期检查
DELAY_CHACK = """
    UPDATE borrow_record
    SET `status` = '延期'
    WHERE return_time IS NULL AND DATE(NOW()) > DATE(return_deadline)
"""

# 归还档案
RETURN_ARCHIVES = """
    UPDATE borrow_record bc LEFT JOIN archives_file af ON bc.archive_id = af.id
    SET bc.`return_time` = NOW(),
        bc.is_delay = ( CASE WHEN `status` = '延期' THEN 1 ELSE 0 END ),
        af.file_status = '在库', bc.return_operator_id = %s,
        bc.status = '归还'
    WHERE bc.id = %s AND bc.return_time IS NULL
"""

# 查询归还记录总数
SELECT_RETURN_RECORDS_COUNT = """
    SELECT COUNT(*)
    FROM borrow_record bc
        LEFT JOIN archives_file af
        ON bc.archive_id = af.id
    WHERE
        bc.return_time IS NOT NULL
"""

# 查询归还记录列表
SELECT_RETURN_RECORDS = """
    SELECT
        af.serial, af.title, bc.archive_id, bc.id,
        date_format(bc.return_deadline, '%Y-%m-%d') AS return_deadline,
        ( CASE WHEN bc.is_delay = 1 THEN '延期' ELSE '无' END ) AS is_delay,
        CONCAT(ac.code, '柜-', al.code, '格-', ab.code, '盒') AS location,
        bc.borrower, date_format(bc.return_time, '%Y-%m-%d') AS return_time,
        omc.realname AS operator
    FROM
        borrow_record bc
        LEFT JOIN archives_file af ON bc.archive_id = af.id
        LEFT JOIN archive_box ab ON af.box_id = ab.id
        LEFT JOIN archive_lattice al ON af.lattice_id = al.id
        LEFT JOIN archive_cabinet ac ON af.cabinet_id = ac.id
        LEFT JOIN organize_manager_cloudauthuser omc
        ON bc.return_operator_id = omc.id
    WHERE
        bc.return_time IS NOT NULL
"""